package com.crm.excel.upload;

import java.math.BigDecimal;
import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;

import com.crm.pojo.ClientInfo;

public class ClientInfoExcel extends BaseExcel<ClientInfo> {

	@SuppressWarnings("deprecation")
	@Override
	public List<ClientInfo> excelValue(Workbook wb) throws ParseException {
		// 得到第一个shell
		Sheet sheet = wb.getSheetAt(0);
		// 得到Excel的行数
		this.totalRows = sheet.getPhysicalNumberOfRows();
		// 得到Excel的列数(前提是有行数)
		if (totalRows > 1 && sheet.getRow(0) != null) {
			this.totalCells = sheet.getRow(0).getPhysicalNumberOfCells();
		}
		List<ClientInfo> clientInfos = new ArrayList<>();

		DateFormat fmt =new SimpleDateFormat("dd-MM月 -yy");
		//		// 循环Excel行数
		for (int r = 1; r < totalRows; r++) {
			Row row = sheet.getRow(r);
			if (row == null) {
				continue;
			}
			ClientInfo clientInfo = new ClientInfo();
			// 循环Excel的列
			for (int c = 0; c < this.totalCells; c++) {
				Cell cell = row.getCell(c);
				if (null != cell) {
					if (c == 0) {
						// 如果是纯数字,比如你写的是25,cell.getNumericCellValue()获得是25.0,通过截取字符串去掉.0获得25
						if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {
							String cidStr = String.valueOf(cell.getNumericCellValue());
							cidStr = cidStr.substring(0, cidStr.length() - 2 > 0 ? cidStr.length() - 2 : 1);
							clientInfo.setCid(Long.parseLong(cidStr));
						} else {
							if (cell.getStringCellValue()!=null && !cell.getStringCellValue().isEmpty()) {
								clientInfo.setCid(Long.parseLong(cell.getStringCellValue()));// ID
							}
						}
					} else if (c == 1) {
						if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {
							String cname = String.valueOf(cell.getNumericCellValue());
							clientInfo.setCname(cname.substring(0, cname.length() - 2 > 0 ? cname.length() - 2 : 1));// 客户名称
						} else {
							clientInfo.setCname(cell.getStringCellValue());// 客户名称
						}
					} else if (c == 2) {
						if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {
							String importantStr = String.valueOf(cell.getNumericCellValue());
							importantStr = importantStr.substring(0,
									importantStr.length() - 2 > 0 ? importantStr.length() - 2 : 1);
							BigDecimal important = new BigDecimal(importantStr);
							clientInfo.setImportant(important);// 重要程度
						} else {
							if (cell.getStringCellValue()!=null && !cell.getStringCellValue().isEmpty()) {
								clientInfo.setImportant(new BigDecimal(cell.getStringCellValue()));// 重要程度
							}
						}
					} else if (c == 3) {
						if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {
							String updateDateStr = String.valueOf(cell.getNumericCellValue());
							updateDateStr = updateDateStr.substring(0,
									updateDateStr.length() - 2 > 0 ? updateDateStr.length() - 2 : 1);// 更新日期
							clientInfo.setUpdateDate(fmt.parse(updateDateStr));// 更新日期
						} else {
							if (cell.getStringCellValue()!=null && !cell.getStringCellValue().isEmpty()) {
								clientInfo.setUpdateDate(fmt.parse(cell.getStringCellValue()));// 更新日期
							}
						}
					} else if (c == 4) {
						if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {
							String createDateStr = String.valueOf(cell.getNumericCellValue());
							createDateStr = createDateStr.substring(0,
									createDateStr.length() - 2 > 0 ? createDateStr.length() - 2 : 1);// 创建日期
							Date createDate = fmt.parse(createDateStr);
							clientInfo.setCreateDate(createDate);// 创建日期
						} else {
							if (cell.getStringCellValue()!=null && !cell.getStringCellValue().isEmpty()) {
								clientInfo.setCreateDate(fmt.parse(cell.getStringCellValue()));// 创建日期
							}
						}
					} else if (c == 5) {
						if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {
							String userIdStr = String.valueOf(cell.getNumericCellValue());
							userIdStr = userIdStr.substring(0, userIdStr.length() - 2 > 0 ? userIdStr.length() - 2 : 1);
							clientInfo.setUserId(new BigDecimal(userIdStr));// 用户编号
						} else {
							if (cell.getStringCellValue()!=null && !cell.getStringCellValue().isEmpty()) {
								clientInfo.setUserId(new BigDecimal(cell.getStringCellValue()));// 用户编号
							}
						}
					} else if (c == 6) {
						if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {
							String industry = String.valueOf(cell.getNumericCellValue());
							clientInfo.setIndustry(industry.substring(0, industry.length() - 2 > 0 ? industry.length() - 2 : 1));// 行业
						} else {
							clientInfo.setIndustry(cell.getStringCellValue());// 行业
						}
					} else if (c == 7) {
						if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {
							String scale = String.valueOf(cell.getNumericCellValue());
							clientInfo.setScale(scale.substring(0, scale.length() - 2 > 0 ? scale.length() - 2 : 1));// 规模
						} else {
							clientInfo.setScale(cell.getStringCellValue());// 规模
						}
					} else if (c == 8) {
						if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {
							String proflie = String.valueOf(cell.getNumericCellValue());
							clientInfo.setProflie(
									proflie.substring(0, proflie.length() - 2 > 0 ? proflie.length() - 2 : 1));// 公司简介
						} else {
							clientInfo.setProflie(cell.getStringCellValue());// 公司简介
						}
					} else if (c == 9) {
						if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {
							String source = String.valueOf(cell.getNumericCellValue());
							clientInfo.setSource(source.substring(0, source.length() - 2 > 0 ? source.length() - 2 : 1));// 客户来源
						} else {
							clientInfo.setSource(cell.getStringCellValue());// 客户来源
						}
					} else if (c == 10) {
						if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {
							String stage = String.valueOf(cell.getNumericCellValue());
							clientInfo.setStage(stage.substring(0, stage.length() - 2 > 0 ? stage.length() - 2 : 1));// 阶段
						} else {
							clientInfo.setStage(cell.getStringCellValue());// 阶段
						}
					} else if (c == 11) {
						if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {
							String relationship = String.valueOf(cell.getNumericCellValue());
							clientInfo.setRelationship(relationship.substring(0,
									relationship.length() - 2 > 0 ? relationship.length() - 2 : 1));// 关系等级
						} else {
							clientInfo.setRelationship(cell.getStringCellValue());// 关系等级
						}
					} else if (c == 12) {
						if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {
							String credit = String.valueOf(cell.getNumericCellValue());
							clientInfo
									.setCredit(credit.substring(0, credit.length() - 2 > 0 ? credit.length() - 2 : 1));// 信用等级
						} else {
							clientInfo.setCredit(cell.getStringCellValue());// 信用等级
						}
					} else if (c == 13) {
						if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {
							String pay = String.valueOf(cell.getNumericCellValue());
							clientInfo.setPay(pay.substring(0, pay.length() - 2 > 0 ? pay.length() - 2 : 1));// 支付方式
						} else {
							clientInfo.setPay(cell.getStringCellValue());// 支付方式
						}
					} else if (c == 14) {
						if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {
							String phone = String.valueOf(cell.getNumericCellValue());
							clientInfo.setPhone(phone.substring(0, phone.length() - 2 > 0 ? phone.length() - 2 : 1));// 手机号码
						} else {
							clientInfo.setPhone(cell.getStringCellValue());// 手机号码
						}
					} else if (c == 15) {
						if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {
							String fax = String.valueOf(cell.getNumericCellValue());
							clientInfo.setFax(fax.substring(0, fax.length() - 2 > 0 ? fax.length() - 2 : 1));// 传真
						} else {
							clientInfo.setFax(cell.getStringCellValue());// 传真
						}
					} else if (c == 16) {
						if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {
							String url = String.valueOf(cell.getNumericCellValue());
							clientInfo.setUrl(url.substring(0, url.length() - 2 > 0 ? url.length() - 2 : 1));// 网址
						} else {
							clientInfo.setUrl(cell.getStringCellValue());// 网址
						}
					} else if (c == 17) {
						if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {
							String local = String.valueOf(cell.getNumericCellValue());
							clientInfo.setLocal(local.substring(0, local.length() - 2 > 0 ? local.length() - 2 : 1));// 地区
						} else {
							clientInfo.setLocal(cell.getStringCellValue());// 地区
						}
					} else if (c == 18) {
						if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {
							String address = String.valueOf(cell.getNumericCellValue());
							clientInfo.setAddress(
									address.substring(0, address.length() - 2 > 0 ? address.length() - 2 : 1));// 地址
						} else {
							clientInfo.setAddress(cell.getStringCellValue());// 地址
						}
					} else if (c == 19) {
						if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {
							String remarks = String.valueOf(cell.getNumericCellValue());
							clientInfo.setRemarks(
									remarks.substring(0, remarks.length() - 2 > 0 ? remarks.length() - 2 : 1));// 备注
						} else {
							clientInfo.setRemarks(cell.getStringCellValue());// 备注
						}
					} else if (c == 20) {
						if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {
							String type = String.valueOf(cell.getNumericCellValue());
							clientInfo.setType(type.substring(0, type.length() - 2 > 0 ? type.length() - 2 : 1));// 类型
						} else {
							clientInfo.setType(cell.getStringCellValue());// 类型
						}
						
					} else if (c == 21) {
						if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {
							String clientInfoIsDel = String.valueOf(cell.getNumericCellValue());
							clientInfo.setClientInfoIsDel(clientInfoIsDel.substring(0,
									clientInfoIsDel.length() - 2 > 0 ? clientInfoIsDel.length() - 2 : 1));// 是否删除
						} else {
							clientInfo.setClientInfoIsDel(cell.getStringCellValue());// 是否删除
						}
					}
				}
			}
			clientInfos.add(clientInfo);
		}
		return clientInfos;
	}

}
